"""
Copyright (c) 2023 - present SKbioinfo
"""

from flask_restful import Resource, request,reqparse
from flask import jsonify

from apps.API.project.db_project import *
from apps.API.utils.log_helper import lg
from apps.API.connector.db_common import *
from apps.API.common.db_table_config import *
from apps.API.common.excel_format import add_form,trans
import pandas as pd

'''
    Sample infos
'''
class interfaceSample(Resource):
    # @login_required
    def get(self):
        try:
            ### SQL: query sample info ###
            cols = '''
                id,
                project_id,
                client_id,
                sample_id,
                sample_name,
                lot_number,
                size,
                sample_amount,
                current_amount,
                disp_amount,
                disp_size,
                arrive_time,
                instock_time,
                create_time,
                modify_time,
                status
            '''
            sample_query_info = '''
            SELECT {cols} FROM {table_name}
            '''.format(
                cols=cols,
                table_name=common_tables.sample_db["name"]
            )
            sam_infos=dic_query_info(sample_query_info)
            final_infos = {}
            samples = []
            for sample in sam_infos:
                sample["arrive_time"]=sample["arrive_time"].split()[0]
                sample["instock_time"]=shift_format(sample["instock_time"])
                sample["create_time"]=shift_format(sample["create_time"])
                sample["modify_time"]=shift_format(sample["modify_time"])
                samples.append(sample)
            final_infos = {
                "pageTotal":len(sam_infos),
                "samples":samples
            }
            response=jsonify(final_infos)
            response.status=202
            return response
        except Exception as e:
            lg.error(e)
    
    def post(self):
        try:
            select_proj_ids = request.json["project_id"]
            query_proj_list = ""
            for project_id in select_proj_ids:
                query_proj_list += "\"" + project_id + "\","
            query_proj_list=query_proj_list.rstrip(",")
            ### SQL: query selected projects ###
            cols = '''
                id,
                project_id,
                client_id,
                sample_id,
                sample_name,
                lot_number,
                size,
                sample_amount,
                current_amount,
                disp_amount,
                disp_size,
                arrive_time,
                instock_time,
                create_time,
                modify_time,
                status
            '''
            query_proj_sql='''
                SELECT {cols} FROM {table_name} WHERE {select_col} IN ({select_values})
            '''.format(
                cols=cols,
                table_name=common_tables.sample_db["name"],
                select_col="project_id",
                select_values=query_proj_list
            )
            base_infos=dic_query_info(query_proj_sql)
            response=jsonify(base_infos)
            response.status_code = 202
            return response
        except Exception as e:
            lg.error(e)
    
class interfaceSampleID(Resource):
    def delete(self,id):
        try:
            ### SQL: delete project in database ###
            delete_sql = '''DELETE FROM {table_name} WHERE {db_name} = \"{col_value}\"'''.format(
                table_name=common_tables.sample_db["name"],
                col_value=id,
                db_name="id"
            )
            delete_info(delete_sql = delete_sql)
            return 202
        except Exception as e:
            lg.error(e)
            
    def put(self,id):
        try:
            if request.json["disp_amount"] == None:
                request.json["disp_amount"] = 0
            ### SQL: update sample info ###
            update_sample_sql='''
                UPDATE {table_name} SET 
                    project_id=\"{project_id}\",
                    client_id=\"{client_id}\",
                    sample_id=\"{sample_id}\",
                    sample_name=\"{sample_name}\",
                    lot_number=\"{lot_number}\",
                    size=\"{size}\",
                    sample_amount=\"{sample_amount}\",
                    disp_amount={disp_amount},
                    current_amount={current_amount}
                WHERE {col_name}=\"{sample_id}\"
            '''.format(
                    table_name=common_tables.sample_db["name"],
                    project_id=request.json["project_id"],
                    client_id=request.json["client_id"],
                    sample_id=request.json["sample_id"],
                    sample_name=request.json["sample_name"],
                    lot_number=request.json["lot_number"],
                    size=request.json["size"],
                    disp_amount=request.json["disp_amount"],
                    current_amount=request.json["current_amount"],
                    sample_amount=request.json["sample_amount"],
                    col_name="sample_id",
            )
            update_info(update_sample_sql)
            return 202
        except Exception as e:
            lg.error(e)
            return 404 
        
class interfaceSampleAdd(Resource):
    def post(self):
        try:
            cols='''
                project_id,
                client_id,
                sample_id,
                sample_name,
                lot_number,
                size,
                sample_amount,
                current_amount,
                arrive_time,
                create_time,
                instock_time,
                modify_time,
                status
            '''
            values='''
                \"{project_id}\",
                \"{client_id}\",
                \"{sample_id}\",
                \"{sample_name}\",
                \"{lot_number}\",
                \"{size}\",
                {sample_amount},
                {current_amount},
                \"{arrive_time}\",
                \"{create_time}\",
                \"{instock_time}\",
                \"{modify_time}\",
                {status}
            '''.format(
                project_id=request.json["project_id"],
                client_id=request.json["client_id"],
                sample_id=request.json["sample_id"],
                sample_name=request.json["sample_name"],
                lot_number=request.json["lot_number"],
                size=request.json["size"],
                sample_amount=request.json["sample_amount"],
                current_amount=0,
                arrive_time=request.json["arrive_time"],
                instock_time=get_current_time(),
                create_time=get_current_time(),
                modify_time=get_current_time(),
                status=0
            )
            ### SQL: sample add ###
            sample_add_sql = '''
                INSERT INTO {table_name} ({cols}) VALUES ({values})
            '''.format(
                    table_name=common_tables.sample_db["name"],
                    cols=cols,
                    values=values
            )
            ### STEP 1: add sample ###
            add_info(sample_add_sql)
            return 202
        except Exception as e:
            lg.error(e)
            return 400

class interfaceSampleUpload(Resource):
    def __init__(self):
        self.table_name=common_tables.sample_db["name"]
    
    def compare_previous_infos(self,file_df):
        ### SQL: query previous projects ###
        query_previous_sql='''
            SELECT 
                project_id,
                sample_id
            FROM {table_name}
        '''.format(
            table_name = self.table_name
        )
        ### query base info ###
        base_info = dic_query_info(query_previous_sql)
        ### previous list ###
        previous_list = []
        for info in base_info:
            tmp_info = info["project_id"] + "_" + info["sample_id"]
            previous_list.append(tmp_info)
        previous_list = list(set(previous_list))
        ### compare previous infos ###
        for previous_info in previous_list:
            tmp=previous_info.split("_")
            for key,value in file_df.iterrows():
                if value["样品编号"] == tmp[1] and value["项目编号"] == tmp[0]:
                    file_df=file_df.drop(key)
        return file_df
        
    def post(self):
        try:
            ### read excel file ###
            file=request.files.get("file")
            file_df=pd.read_excel(file)
            file_df=file_df.dropna(axis=0, how='any',subset=None, inplace=False)
            file_df=self.compare_previous_infos(file_df)
            sample_form = add_form.sample_receipt
            ### SQL:sample instock ###
            sam_cols='''
                project_id,
                client_id,
                sample_id,
                sample_name,
                lot_number,
                size,
                sample_amount,
                current_amount,
                arrive_time,
                instock_time,
                create_time,
                modify_time,
                status
            '''
            for key,value in file_df.iterrows():
                sam_values='''
                    \"{project_id}\",
                    \"{client_id}\",
                    \"{sample_id}\",
                    \"{sample_name}\",
                    \"{lot_number}\",
                    \"{size}\",
                    {sample_amount},
                    {current_amount},
                    \"{arrive_time}\",
                    \"{instock_time}\",
                    \"{create_time}\",
                    \"{modify_time}\",
                    {status}
                '''.format(
                    project_id=value[sample_form["project_id"]],
                    client_id=value[sample_form["client_id"]],
                    sample_id=value[sample_form["sample_id"]],
                    sample_name=value[sample_form["sample_name"]],
                    lot_number=value[sample_form["lot_number"]],
                    size=value[sample_form["size"]],
                    sample_amount=float(value[sample_form["sample_amount"]]),
                    current_amount=0,
                    arrive_time=value[sample_form["arrive_time"]],
                    instock_time=get_current_time(),
                    create_time=get_current_time(),
                    modify_time=get_current_time(),
                    status=0
                ) 
                '''
                    SQL: sample instock sql
                '''
                sample_instock_sql = '''
                    INSERT INTO {table_name}
                    ({cols}) VALUES ({values})
                '''.format(
                    table_name=common_tables.sample_db["name"],
                    cols=sam_cols,
                    values=sam_values
                )
                '''
                    SQL: project sql
                '''
                proj_cols = '''
                    project_id,
                    project_name,
                    project_level,
                    client_id,
                    end_time,
                    contract_time,
                    create_time,
                    modify_time
                '''
                proj_values = '''
                    \"{project_id}\",
                    \"{project_name}\",
                    \"{project_level}\",
                    \"{client_id}\",
                    \"{end_time}\",
                    \"{contract_time}\",
                    \"{create_time}\",
                    \"{modify_time}\"
                '''.format(
                    project_id=value[sample_form["project_id"]],
                    project_name=value[sample_form["project_name"]],
                    project_level=trans.project_level[value[sample_form["project_level"]]],
                    client_id=value[sample_form["client_id"]],
                    end_time=get_current_time(),
                    contract_time=value[sample_form["contract_time"]],
                    create_time=get_current_time(),
                    modify_time=get_current_time()
                )
                project_sql = '''
                    INSERT INTO {table_name}
                    ({cols}) VALUES ({values})
                '''.format(
                    table_name=common_tables.project_db["name"],
                    cols=proj_cols,
                    values=proj_values
                )
                ### STEP 1: add sample ###
                add_info(sample_instock_sql)
                ### STEP 2: add project ###
                # add_info(project_sql)
            return 202
        except Exception as e:
            lg.error(e)
            return 400

class interfaceSampleInstock(Resource):
    def post(self,action):
        try:
            update_status_sql='''
                UPDATE {table_name} SET status={value} WHERE sample_id=\"{sample_id}\"
            '''
            if action == "check":
                update_check_status=update_status_sql.format(
                    table_name=common_tables.sample_db["name"],
                    value=1,
                    sample_id=request.json["sample_id"]
                )
                ### STEP 1: update check status ###
                update_info(update_check_status)
                update_disp_condition='''
                    UPDATE {table_name} SET disp_amount=0,disp_size="-"
                    WHERE sample_id=\"{sample_id}\"
                '''.format(
                    table_name=common_tables.sample_db["name"],
                    sample_id=request.json["sample_id"]
                )
                ### STEP 2: update disp condition ###
                update_info(update_disp_condition) 
            if action == "instock":
                ### SQL: update instock tag ### 
                update_instock_status=update_status_sql.format(
                    table_name=common_tables.sample_db["name"],
                    value=2,
                    sample_id=request.json["sample_id"]
                )
                ### SQL: instock sample amount ###
                update_instock_info='''
                    UPDATE {table_name} SET 
                        {col_one}={val_one},
                        {col_two}=\"{val_two}\" 
                    WHERE sample_id=\"{sample_id}\"
                '''.format(
                        table_name=common_tables.sample_db["name"],
                        col_one="current_amount",
                        val_one=request.json["sample_amount"],
                        col_two="instock_time",
                        val_two=get_current_time(),
                        sample_id=request.json["sample_id"]
                )
                ### STEP 1: update sample tag ###
                update_info(update_instock_status)
                ### STEP 2: update sample amount ###
                update_info(update_instock_info)
            if action == "over":
                ### SQL: update instock tag ### 
                update_instock_status=update_status_sql.format(
                    table_name=common_tables.sample_db["name"],
                    value=4,
                    sample_id=request.json["sample_id"]
                )
                ### STEP 1: update sample tag ###
                update_info(update_instock_status)
        except Exception as e:
            lg.error(e)